using System;
using System.Collections.Generic;
using System.Text;

using System.Data.Common;

using MakeIT.Framework.Data;
using MakeIT.SGI.ERP.Entidades;

namespace MakeIT.SGI.ERP.Negocios
{
	public partial class SgiDAOT : DAOT
	{

		public CcustoVO getCcusto(int? idCCusto, string cCusto)
		{
			CcustoVO ccustoVO = null;
			List<CcustoVO> listCcustoVOs = getCcustos(idCCusto, cCusto, null, null);
			if (listCcustoVOs != null && listCcustoVOs.Count > 0)
			{
				ccustoVO = listCcustoVOs[0];
			}
			return ccustoVO;
		}

		public CcustoVO getCcusto(int? idCCusto)
		{
			return getCcusto(idCCusto, null);
		}

		public List<CcustoVO> getCcustos()
		{
			return getCcustos(null, null, null, null);
		}

		public List<CcustoVO> getCcustos(int? idCCusto)
		{
			return getCcustos(idCCusto, null, null, null);
		}

		public List<CcustoVO> getCcustos(int? idCCusto, string cCusto)
		{
			return getCcustos(idCCusto, cCusto, null, null);
		}

		public List<CcustoVO> getCcustos(int? idCCusto, string cCusto, int? idFilial, int? idCCustoPai)
		{
			string where = "WHERE ";
			StringBuilder query = new StringBuilder();

			query.AppendLine("SELECT cct.idCCusto as cct_idCCusto, cct.cCusto as cct_cCusto, cct.idFilial as cct_idFilial, fil.filial as fil_filial, cct.idCCustoPai as cct_idCCustoPai, cct2.cCusto as cct2_cCusto, cct.cadastro as cct_cadastro, cct.cadastradoPor as cct_cadastradoPor, cct.edicao as cct_edicao, cct.editadoPor as cct_editadoPor  ");
			query.AppendLine("FROM ccusto as cct LEFT OUTER JOIN filial as fil on cct.idFilial=fil.idFilial  LEFT OUTER JOIN ccusto as cct2 on cct.idCCustoPai=cct2.idCCusto  ");
			if (idCCusto != null)
			{
				query.AppendLine(where + "cct.idCCusto=@idCCusto ");
				where = "AND ";
			}
			if (cCusto != null)
			{
				query.AppendLine(where + "cct.cCusto LIKE @cCusto ");
				where = "AND ";
			}
			if (idFilial != null)
			{
				query.AppendLine(where + "cct.idFilial=@idFilial ");
				where = "AND ";
			}
			if (idCCustoPai != null)
			{
				query.AppendLine(where + "cct.idCCustoPai=@idCCustoPai ");
				where = "AND ";
			}


			query.AppendLine("ORDER BY cct.cCusto ");

			DbCommand cmd = this.getCommand();
			cmd.CommandText = query.ToString();

			if (idCCusto!= null) cmd.Parameters.Add(getParameter(cmd, "@idCCusto", idCCusto));
			if (cCusto!= null) cmd.Parameters.Add(getParameter(cmd, "@cCusto", "%"+cCusto+"%"));
			if (idFilial!= null) cmd.Parameters.Add(getParameter(cmd, "@idFilial", idFilial));
			if (idCCustoPai!= null) cmd.Parameters.Add(getParameter(cmd, "@idCCustoPai", idCCustoPai));


			DbDataReader reader = null;
			try
			{
				reader = cmd.ExecuteReader();

				List<CcustoVO> listCcusto = new List<CcustoVO>();

				while (reader.Read())
				{
					CcustoVO ccustoVO = CcustoVO.CreateVO();
					ccustoVO.idCCusto = reader.GetInt32(reader.GetOrdinal("cct_idCCusto"));
					ccustoVO.cCusto = reader.GetString(reader.GetOrdinal("cct_cCusto"));
					ccustoVO.idFilial = reader.GetInt32(reader.GetOrdinal("cct_idFilial"));
					if (!reader.IsDBNull(reader.GetOrdinal("fil_filial")))
						ccustoVO.idFilialVO.filial = reader.GetString(reader.GetOrdinal("fil_filial"));
                    if (!reader.IsDBNull(reader.GetOrdinal("cct_idCCustoPai")) && reader.GetOrdinal("cct_idCCustoPai") != -1)
					    ccustoVO.idCCustoPai = reader.GetInt32(reader.GetOrdinal("cct_idCCustoPai"));
                    else
                        ccustoVO.idCCustoPai = -1;
					if (!reader.IsDBNull(reader.GetOrdinal("cct2_cCusto")))
						ccustoVO.idCCustoPaiVO.cCusto = reader.GetString(reader.GetOrdinal("cct2_cCusto"));
					ccustoVO.cadastro = reader.GetDateTime(reader.GetOrdinal("cct_cadastro"));
					ccustoVO.cadastradoPor = reader.GetString(reader.GetOrdinal("cct_cadastradoPor"));
					ccustoVO.edicao = reader.GetDateTime(reader.GetOrdinal("cct_edicao"));
					ccustoVO.editadoPor = reader.GetString(reader.GetOrdinal("cct_editadoPor"));


					listCcusto.Add(ccustoVO);
				}
				reader.Close();
				return listCcusto;
			}
			catch (Exception ex) { throw ex; }
			finally
			{
				if (reader != null && !reader.IsClosed)
					reader.Close();
			}
		}

		public void setCcusto(CcustoVO ccustoVO)
		{
			StringBuilder query = new StringBuilder();

			query.AppendLine("UPDATE ccusto ");
			query.AppendLine("SET cCusto=@cCusto, idFilial=@idFilial, idCCustoPai=@idCCustoPai,  cadastro=@cadastro, cadastradoPor=@cadastradoPor, edicao=@edicao, editadoPor=@editadoPor ");
			query.AppendLine("WHERE idCCusto=@idCCusto ");

			DbCommand cmd = this.getCommand();
			cmd.CommandText = query.ToString();

			cmd.Parameters.Add(getParameter(cmd, "@idCCusto", ccustoVO.idCCusto));
			cmd.Parameters.Add(getParameter(cmd, "@cCusto", ccustoVO.cCusto));
			cmd.Parameters.Add(getParameter(cmd, "@idFilial", ccustoVO.idFilial));
            if (ccustoVO.idCCustoPai != -1 && !String.IsNullOrEmpty(Convert.ToString(ccustoVO.idCCustoPai)))
                cmd.Parameters.Add(getParameter(cmd, "@idCCustoPai", ccustoVO.idCCustoPai));
            else
                cmd.Parameters.Add(getParameter(cmd, "@idCCustoPai", System.DBNull.Value));
			cmd.Parameters.Add(getParameter(cmd, "@cadastro", ccustoVO.cadastro));
			cmd.Parameters.Add(getParameter(cmd, "@cadastradoPor", ccustoVO.cadastradoPor));
			cmd.Parameters.Add(getParameter(cmd, "@edicao", ccustoVO.edicao));
			cmd.Parameters.Add(getParameter(cmd, "@editadoPor", ccustoVO.editadoPor));

			try
			{
				cmd.ExecuteNonQuery();
			}
			catch (Exception ex) { throw ex; }

		}

		public long newCcusto(CcustoVO ccustoVO)
		{
			StringBuilder query = new StringBuilder();

			query.AppendLine("INSERT INTO ccusto ");
			query.AppendLine("  ( cCusto, idFilial, idCCustoPai, cadastro, cadastradoPor, edicao, editadoPor ) ");
			query.AppendLine("VALUES ( @cCusto, @idFilial, @idCCustoPai, @cadastro, @cadastradoPor, @edicao, @editadoPor ) ");

			DbCommand cmd = this.getCommand();
			cmd.CommandText = query.ToString();

			cmd.Parameters.Add(getParameter(cmd, "@cCusto", ccustoVO.cCusto));
			cmd.Parameters.Add(getParameter(cmd, "@idFilial", ccustoVO.idFilial));
            if(ccustoVO.idCCustoPai != -1 && !String.IsNullOrEmpty(Convert.ToString(ccustoVO.idCCustoPai)))
			    cmd.Parameters.Add(getParameter(cmd, "@idCCustoPai", ccustoVO.idCCustoPai));
            else
                cmd.Parameters.Add(getParameter(cmd, "@idCCustoPai", System.DBNull.Value));
			cmd.Parameters.Add(getParameter(cmd, "@cadastro", ccustoVO.cadastro));
			cmd.Parameters.Add(getParameter(cmd, "@cadastradoPor", ccustoVO.cadastradoPor));
			cmd.Parameters.Add(getParameter(cmd, "@edicao", ccustoVO.edicao));
			cmd.Parameters.Add(getParameter(cmd, "@editadoPor", ccustoVO.editadoPor));


			try
			{
				cmd.ExecuteNonQuery();

				long newID = 0;
				if (tipo.Equals(DBType.MSSql))
				{
					cmd.CommandText = "SELECT IDENT_CURRENT('ccusto') AS 'Identity' ";
					DbDataReader reader = cmd.ExecuteReader();
					reader.Read();
					newID = (long)reader.GetDecimal(reader.GetOrdinal("Identity"));
					reader.Close();
				}
				else if (tipo.Equals(DBType.MySql))
				{
					newID = ((MySql.Data.MySqlClient.MySqlCommand)cmd).LastInsertedId;
				}
				else if (tipo.Equals(DBType.ODBC))
				{
					//TODO implementar
				}
				else if (tipo.Equals(DBType.ORACLE))
				{
					//TODO implementar
				}
				return newID;
			}
			catch (Exception ex) { throw ex; }

		}

		public void delCcusto(CcustoVO ccustoVO)
		{
			StringBuilder query = new StringBuilder();

			query.AppendLine("DELETE FROM ccusto ");
			query.AppendLine("WHERE idCCusto=@idCCusto ");

			DbCommand cmd = getCommand();
			cmd.CommandText = query.ToString();

			cmd.Parameters.Add(getParameter(cmd, "@idCCusto", ccustoVO.idCCusto));


			try
			{
				cmd.ExecuteNonQuery();
			}
			catch (Exception ex) { throw ex; }

		}
		public int getCountCcustoxIdFilial(int? idFilial)
		{
			StringBuilder query = new StringBuilder();

			query.AppendLine("SELECT COUNT(idFilial) as ocorrencias FROM ccusto WHERE IdFilial=@IdFilial ");

			DbCommand cmd = this.getCommand();
			cmd.CommandText = query.ToString();

			cmd.Parameters.Add(getParameter(cmd, "@IdFilial", idFilial));

			DbDataReader reader = null;
			try
			{
				reader = cmd.ExecuteReader();
				reader.Read();

				int ocorrencias = 0;
				ocorrencias = reader.GetInt32(reader.GetOrdinal("ocorrencias"));
				reader.Close();
				return ocorrencias;
			}
			catch (Exception ex) { throw ex; }
			finally
			{
				if (reader != null && !reader.IsClosed)
					reader.Close();
			}
		}
		public int getCountCcustoxIdCCustoPai(int? idCCustoPai)
		{
			StringBuilder query = new StringBuilder();

			query.AppendLine("SELECT COUNT(idCCustoPai) as ocorrencias FROM ccusto WHERE IdCCustoPai=@IdCCustoPai ");

			DbCommand cmd = this.getCommand();
			cmd.CommandText = query.ToString();

			cmd.Parameters.Add(getParameter(cmd, "@IdCCustoPai", idCCustoPai));

			DbDataReader reader = null;
			try
			{
				reader = cmd.ExecuteReader();
				reader.Read();

				int ocorrencias = 0;
				ocorrencias = reader.GetInt32(reader.GetOrdinal("ocorrencias"));
				reader.Close();
				return ocorrencias;
			}
			catch (Exception ex) { throw ex; }
			finally
			{
				if (reader != null && !reader.IsClosed)
					reader.Close();
			}
		}
	}
}
